{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-colab"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/pathwaycom/pathway/blob/main/examples/notebooks/tutorials/join_manual.ipynb\" target=\"_parent\"><img src=\"https://pathway.com/assets/colab-badge.svg\" alt=\"Run In Colab\" class=\"inline\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "notebook-instructions",
      "source": [
        "# Installing Pathway with Python 3.10+\n",
        "\n",
        "In the cell below, we install Pathway into a Python 3.10+ Linux runtime.\n",
        "\n",
        "> **If you are running in Google Colab, please run the colab notebook (Ctrl+F9)**, disregarding the 'not authored by Google' warning.\n",
        "> \n",
        "> **The installation and loading time is less than 1 minute**.\n"
      ],
      "metadata": {}
    },
    {
      "cell_type": "code",
      "id": "pip-installation-pathway",
      "source": [
        "%%capture --no-display\n",
        "!pip install --prefer-binary pathway"
      ],
      "execution_count": null,
      "outputs": [],
      "metadata": {}
    },
    {
      "cell_type": "code",
      "id": "logging",
      "source": [
        "import logging\n",
        "\n",
        "logging.basicConfig(level=logging.CRITICAL)"
      ],
      "execution_count": null,
      "outputs": [],
      "metadata": {}
    },
    {
      "cell_type": "markdown",
      "id": "4",
      "metadata": {},
      "source": [
        "# Playing with Joins.\n",
        "A brief explanation on how to perform joins with Pathway.\n",
        "\n",
        "Join is one of the basic table operations provided in Pathway.\n",
        "A join operation combines columns from two different tables by associating rows from both tables wich are matching on some given values.\n",
        "\n",
        "This guide presents several samples of code using the joins:\n",
        "*  [join (inner join)](#simple-inner-join)\n",
        "*  [join_left, join_right, join_outer (outer_joins)](#outer-joins)\n",
        "\n",
        "\n",
        "The examples demonstrate usual use-cases, explain the behavior of\n",
        "outer joins, and point out some peculiarities you may encounter\n",
        "while using Pathway. In particular, they show how to:\n",
        "* [inherit id from left or right table](#id-inheritance-in-join)\n",
        "* [join tables using foreign keys](#joins-on-a-foreign-key)\n",
        "* [use joins in a chain](#chaining-joins)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "5",
      "metadata": {},
      "source": [
        "## Prerequisites\n",
        "\n",
        "Be sure to import Pathway."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "6",
      "metadata": {},
      "outputs": [],
      "source": [
        "import pathway as pw"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "7",
      "metadata": {
        "lines_to_next_cell": 2
      },
      "outputs": [],
      "source": [
        "# To use advanced features with Pathway Scale, get your free license key from\n",
        "# https://pathway.com/features and paste it below.\n",
        "# To use Pathway Community, comment out the line below.\n",
        "pw.set_license_key(\"demo-license-key-with-telemetry\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "8",
      "metadata": {},
      "source": [
        "\n",
        "Also, you need some tables to play with. For the sake of presentation,\n",
        "let us consider the following made up scenario: a group of four friends\n",
        "(table friends) goes to grab some breakfast.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "9",
      "metadata": {
        "lines_to_next_cell": 2
      },
      "outputs": [],
      "source": [
        "friends = pw.debug.table_from_markdown(\n",
        "    \"\"\"\n",
        "    | name  |budget\n",
        "  1 | Alice | 13\n",
        "  2 | Bob   | 10\n",
        "  3 | Carol | 11\n",
        "  4 | Dan   | 12\n",
        " \"\"\"\n",
        ")\n",
        "menu = pw.debug.table_from_markdown(\n",
        "    \"\"\"\n",
        "     | dish                              | price\n",
        "  11 | pancakes_with_jam                 | 11\n",
        "  12 | pb_jam_sandwich                   | 9\n",
        "  13 | jam_pb_pancakes                   | 12\n",
        "  14 | scrambled_egg                     | 11\n",
        " \"\"\"\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "10",
      "metadata": {},
      "source": [
        "#\n",
        "However, some of them have allergies (table allergies),\n",
        "and cannot eat everything. Luckily, the restaurant has a list\n",
        "of allergens contained in each dish (table allergens_in_menu).\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "11",
      "metadata": {},
      "outputs": [],
      "source": [
        "allergies = pw.debug.table_from_markdown(\n",
        "    \"\"\"\n",
        "     | person| allergy\n",
        "  21 | Alice | milk\n",
        "  22 | Bob   | eggs\n",
        "  23 | Carol | peanuts\n",
        "  24 | Carol | milk\n",
        " \"\"\"\n",
        ")\n",
        "\n",
        "allergens_in_menu = pw.debug.table_from_markdown(\n",
        "    \"\"\"\n",
        "     | dish                              | allergen\n",
        "  31 | pancakes_with_jam                 | milk\n",
        "  32 | pancakes_with_jam                 | eggs\n",
        "  33 | pb_jam_sandwich                   | peanuts\n",
        "  34 | jam_pb_pancakes                   | eggs\n",
        "  35 | jam_pb_pancakes                   | peanuts\n",
        "  36 | jam_pb_pancakes                   | milk\n",
        "  37 | scrambled_egg                     | eggs\n",
        " \"\"\"\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "12",
      "metadata": {},
      "source": [
        "## Simple inner join\n",
        "### Syntax\n",
        "Putting it simply, the syntax of [`join`](/developers/api-docs/pathway-table#pathway.Table.join) is:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "13",
      "metadata": {},
      "outputs": [],
      "source": [
        "table.join(other, *on)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "14",
      "metadata": {},
      "source": [
        "where\n",
        "* `table` and `other` are tables to be joined,\n",
        "* `*on` is a list of conditions of form:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "15",
      "metadata": {},
      "outputs": [],
      "source": [
        "table.some_column == other.some_other_column"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "16",
      "metadata": {},
      "source": [
        "**Remark:** the order of tables in the condition matters. That is,\n",
        "a condition of the following form won't be accepted:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "17",
      "metadata": {},
      "outputs": [],
      "source": [
        "other.some_other_column == table.some_column"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "18",
      "metadata": {},
      "source": [
        "### Examples\n",
        "As a warm-up, let us see how to make simple join-queries.\n",
        "Let's begin with a simple query that finds all pairs person-dish\n",
        "that can put someone in a hospital."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "19",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | dish              | person\n",
            "^AWSD1V0... | jam_pb_pancakes   | Alice\n",
            "^RGC89RZ... | jam_pb_pancakes   | Bob\n",
            "^Q5HQSD3... | jam_pb_pancakes   | Carol\n",
            "^AWS7XH2... | jam_pb_pancakes   | Carol\n",
            "^AWSDRR3... | pancakes_with_jam | Alice\n",
            "^RGC502A... | pancakes_with_jam | Bob\n",
            "^AWS6ADA... | pancakes_with_jam | Carol\n",
            "^Q5HNMPV... | pb_jam_sandwich   | Carol\n",
            "^RGC7FD2... | scrambled_egg     | Bob\n"
          ]
        }
      ],
      "source": [
        "bad_match = allergies.join(\n",
        "    allergens_in_menu, allergies.allergy == allergens_in_menu.allergen\n",
        ").select(allergens_in_menu.dish, allergies.person)\n",
        "pw.debug.compute_and_print(bad_match)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "20",
      "metadata": {
        "lines_to_next_cell": 2
      },
      "source": [
        "#\n",
        "As you can see, the result is a table of life-threatening pairs.\n",
        "\n",
        "The [`select`](/developers/api-docs/pathway#pathway.JoinResult.select) function works here similarly as [`select`](/developers/api-docs/pathway-table#pathway.Table.select) on a table. The difference is\n",
        "that here, you can use columns of both tables as arguments, e.g."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "21",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | note\n",
            "^AWSD1V0... | Alice can't eat jam_pb_pancakes.\n",
            "^AWSDRR3... | Alice can't eat pancakes_with_jam.\n",
            "^RGC89RZ... | Bob can't eat jam_pb_pancakes.\n",
            "^RGC502A... | Bob can't eat pancakes_with_jam.\n",
            "^RGC7FD2... | Bob can't eat scrambled_egg.\n",
            "^Q5HQSD3... | Carol can't eat jam_pb_pancakes.\n",
            "^AWS7XH2... | Carol can't eat jam_pb_pancakes.\n",
            "^AWS6ADA... | Carol can't eat pancakes_with_jam.\n",
            "^Q5HNMPV... | Carol can't eat pb_jam_sandwich.\n"
          ]
        }
      ],
      "source": [
        "def make_food_constraint_note(name, dish):\n",
        "    return f\"{name} can't eat {dish}.\"\n",
        "\n",
        "\n",
        "bad_match_note = allergies.join(\n",
        "    allergens_in_menu, allergies.allergy == allergens_in_menu.allergen\n",
        ").select(\n",
        "    note=pw.apply(make_food_constraint_note, allergies.person, allergens_in_menu.dish)\n",
        ")\n",
        "pw.debug.compute_and_print(bad_match_note)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "22",
      "metadata": {},
      "source": [
        "*Remark:* note that id is now some auto generated number, which is the usual behavior of join."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "23",
      "metadata": {},
      "source": [
        "### On self joins\n",
        "In order to perform a self join (a join of table with itself),\n",
        "you need to create a copy with [`.copy`](/developers/api-docs/pathway-table#pathway.Table.copy)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "24",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | l_name | r_name\n",
            "^AWS2X9T... | Alice  | Carol\n",
            "^AWS8EGH... | Carol  | Alice\n"
          ]
        }
      ],
      "source": [
        "same_allergies = (\n",
        "    allergies.join(\n",
        "        allergies_copy := allergies.copy(), allergies.allergy == allergies_copy.allergy\n",
        "    )\n",
        "    .select(\n",
        "        l_name=allergies.person,\n",
        "        r_name=allergies_copy.person,\n",
        "    )\n",
        "    .filter(pw.this.l_name != pw.this.r_name)\n",
        ")\n",
        "\n",
        "pw.debug.compute_and_print(same_allergies)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "25",
      "metadata": {},
      "source": [
        "## Outer joins\n",
        "The difference between outer joins and joins is that the outer join adds to the result\n",
        "table also entries that didn't match:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "26",
      "metadata": {},
      "outputs": [],
      "source": [
        "left.join_left(right, conditions)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "27",
      "metadata": {},
      "source": [
        "includes entries from the *left* table and"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "28",
      "metadata": {},
      "outputs": [],
      "source": [
        "left.join_right(right, conditions)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "29",
      "metadata": {},
      "source": [
        "includes entries from the *right* table."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "30",
      "metadata": {},
      "source": [
        "### Simple example\n",
        "To demonstrate outer-joins, you can consider a question about forbidden\n",
        "breakfast configurations (i.e. pairs of person-dish, such that a person is\n",
        "allergic to some ingredients)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "31",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | allergy\n",
            "^GBS8A5Y... | Alice | milk\n",
            "^EDPRSFF... | Bob   | eggs\n",
            "^349MSAF... | Carol | milk\n",
            "^349R9M9... | Carol | peanuts\n",
            "^5K646P9... | Dan   |\n",
            "            | name  | dish\n",
            "^AWS7HPF... | Alice | jam_pb_pancakes\n",
            "^AWS98F7... | Alice | pancakes_with_jam\n",
            "^RGCBXCM... | Bob   | jam_pb_pancakes\n",
            "^RGCAFYZ... | Bob   | pancakes_with_jam\n",
            "^RGCEXE8... | Bob   | scrambled_egg\n",
            "^AWS822S... | Carol | jam_pb_pancakes\n",
            "^Q5HTZJD... | Carol | jam_pb_pancakes\n",
            "^AWSF9MS... | Carol | pancakes_with_jam\n",
            "^Q5HVB48... | Carol | pb_jam_sandwich\n",
            "^VFP6CNZ... | Dan   |\n"
          ]
        }
      ],
      "source": [
        "people_allergies = friends.join_left(\n",
        "    allergies, friends.name == allergies.person\n",
        ").select(friends.name, allergies.allergy)\n",
        "pw.debug.compute_and_print(people_allergies)\n",
        "\n",
        "forbidden_breakfast = people_allergies.join_left(\n",
        "    allergens_in_menu, people_allergies.allergy == allergens_in_menu.allergen\n",
        ").select(people_allergies.name, allergens_in_menu.dish)\n",
        "pw.debug.compute_and_print(forbidden_breakfast)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "32",
      "metadata": {},
      "source": [
        "Now, as you can see, the table has duplicate rows. This is the intended behavior, as\n",
        "a particular person can be allergic to more than one ingredient. In other words,\n",
        "when you add a reason column to your result table, you can see that each row was\n",
        "included in the table above for a different reason."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "33",
      "metadata": {
        "lines_to_next_cell": 2
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | dish              | reason\n",
            "^AWS7HPF... | Alice | jam_pb_pancakes   | milk\n",
            "^AWS98F7... | Alice | pancakes_with_jam | milk\n",
            "^RGCBXCM... | Bob   | jam_pb_pancakes   | eggs\n",
            "^RGCAFYZ... | Bob   | pancakes_with_jam | eggs\n",
            "^RGCEXE8... | Bob   | scrambled_egg     | eggs\n",
            "^AWS822S... | Carol | jam_pb_pancakes   | milk\n",
            "^Q5HTZJD... | Carol | jam_pb_pancakes   | peanuts\n",
            "^AWSF9MS... | Carol | pancakes_with_jam | milk\n",
            "^Q5HVB48... | Carol | pb_jam_sandwich   | peanuts\n",
            "^VFP6CNZ... | Dan   |                   |\n"
          ]
        }
      ],
      "source": [
        "forbidden_breakfast = people_allergies.join_left(\n",
        "    allergens_in_menu, people_allergies.allergy == allergens_in_menu.allergen\n",
        ").select(\n",
        "    people_allergies.name, allergens_in_menu.dish, reason=allergens_in_menu.allergen\n",
        ")\n",
        "pw.debug.compute_and_print(forbidden_breakfast)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "34",
      "metadata": {},
      "source": [
        "### Removing duplicates\n",
        "If you really want to have a table without the 'reason' column and without\n",
        "duplicates, you can achieve that with extra [`groupby`](/developers/api-docs/pathway-table#pathway.Table.groupby) and [`reduce`](/developers/api-docs/pathway#pathway.GroupedTable.reduce) :"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "35",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | dish\n",
            "^MNKENAP... | Alice | jam_pb_pancakes\n",
            "^BVPHA0R... | Alice | pancakes_with_jam\n",
            "^FZ5X7T0... | Bob   | jam_pb_pancakes\n",
            "^HNX75TQ... | Bob   | pancakes_with_jam\n",
            "^85X7DVH... | Bob   | scrambled_egg\n",
            "^822Y9RB... | Carol | jam_pb_pancakes\n",
            "^40QDNWZ... | Carol | pancakes_with_jam\n",
            "^NY6Q5H9... | Carol | pb_jam_sandwich\n",
            "^TER5BA5... | Dan   |\n"
          ]
        }
      ],
      "source": [
        "forbidden_breakfast = people_allergies.join_left(\n",
        "    allergens_in_menu, people_allergies.allergy == allergens_in_menu.allergen\n",
        ").select(people_allergies.name, allergens_in_menu.dish)\n",
        "\n",
        "forbidden_breakfast_no_duplicates = forbidden_breakfast.groupby(\n",
        "    forbidden_breakfast.name, forbidden_breakfast.dish\n",
        ").reduce(forbidden_breakfast.name, forbidden_breakfast.dish)\n",
        "\n",
        "pw.debug.compute_and_print(forbidden_breakfast_no_duplicates)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "36",
      "metadata": {},
      "source": [
        "That simple application of groupby-reduce combination essentially selects a\n",
        "unique set of pairs (name, dish) from our table. More on the reduce-groupby operations\n",
        "can be found in the reduce-groupby [manual](/developers/user-guide/data-transformation/groupby-reduce-manual)."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "37",
      "metadata": {},
      "source": [
        "### Expressions for unmatched rows\n",
        "Because [`join_left`](/developers/api-docs/pathway-table#pathway.Table.join_left) operation returns rows from the left table, even if there is no\n",
        "match in the right column, some input columns for functions might have no defined value.\n",
        "\n",
        "**The left join sets the undefined input cells to `None` and the function needs to\n",
        "explicitly define how to handle such cases.**\n",
        "\n",
        "As an example, you can consider a modified variant of the `bad_match_note` table.\n",
        "The modification is that you want to include all the people, and additionally you want to display their budget.\n",
        "\n",
        "To that end, it is enough that you join the `friends` table with the `bad_match` table, as\n",
        "together they include all necessary information."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "38",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | budget | note\n",
            "^GBSA866... | Alice | 13     | Alice can't eat jam_pb_pancakes.\n",
            "^GBS0MQ3... | Alice | 13     | Alice can't eat pancakes_with_jam.\n",
            "^EDPZCDK... | Bob   | 10     | Bob can't eat jam_pb_pancakes.\n",
            "^EDPQRGD... | Bob   | 10     | Bob can't eat pancakes_with_jam.\n",
            "^EDPJWHD... | Bob   | 10     | Bob can't eat scrambled_egg.\n",
            "^349T7Y9... | Carol | 11     | Carol can't eat jam_pb_pancakes.\n",
            "^349Y3RY... | Carol | 11     | Carol can't eat jam_pb_pancakes.\n",
            "^349T1BM... | Carol | 11     | Carol can't eat pancakes_with_jam.\n",
            "^349GS41... | Carol | 11     | Carol can't eat pb_jam_sandwich.\n",
            "^5K646P9... | Dan   | 12     | Dan can't eat None.\n"
          ]
        }
      ],
      "source": [
        "basic_customer_info = friends.join_left(\n",
        "    bad_match, friends.name == bad_match.person\n",
        ").select(\n",
        "    friends.name,\n",
        "    friends.budget,\n",
        "    note=pw.apply(make_food_constraint_note, friends.name, bad_match.dish),\n",
        ")\n",
        "pw.debug.compute_and_print(basic_customer_info)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "39",
      "metadata": {
        "lines_to_next_cell": 2
      },
      "source": [
        "As you can see, the behavior of the original `make_food_constraint_note` generates a\n",
        "little bit of an odd entry for Dan. To fix that problem, you can redefine the `make_food_constraint_note`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "40",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | budget | note\n",
            "^GBSA866... | Alice | 13     | Alice can't eat jam_pb_pancakes.\n",
            "^GBS0MQ3... | Alice | 13     | Alice can't eat pancakes_with_jam.\n",
            "^EDPZCDK... | Bob   | 10     | Bob can't eat jam_pb_pancakes.\n",
            "^EDPQRGD... | Bob   | 10     | Bob can't eat pancakes_with_jam.\n",
            "^EDPJWHD... | Bob   | 10     | Bob can't eat scrambled_egg.\n",
            "^349T7Y9... | Carol | 11     | Carol can't eat jam_pb_pancakes.\n",
            "^349Y3RY... | Carol | 11     | Carol can't eat jam_pb_pancakes.\n",
            "^349T1BM... | Carol | 11     | Carol can't eat pancakes_with_jam.\n",
            "^349GS41... | Carol | 11     | Carol can't eat pb_jam_sandwich.\n",
            "^5K646P9... | Dan   | 12     | Dan has no food restrictions.\n"
          ]
        }
      ],
      "source": [
        "def make_food_constraint_note_none_tolerant(name, dish):\n",
        "    if dish is None:\n",
        "        return f\"{name} has no food restrictions.\"\n",
        "    else:\n",
        "        return make_food_constraint_note(name, dish)\n",
        "\n",
        "\n",
        "basic_customer_info = friends.join_left(\n",
        "    bad_match, friends.name == bad_match.person\n",
        ").select(\n",
        "    friends.name,\n",
        "    friends.budget,\n",
        "    note=pw.apply(\n",
        "        make_food_constraint_note_none_tolerant,\n",
        "        friends.name,\n",
        "        bad_match.dish,\n",
        "    ),\n",
        ")\n",
        "pw.debug.compute_and_print(basic_customer_info)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "41",
      "metadata": {},
      "source": [
        "To once again demonstrate the fact that the arguments for expressions are replaced with\n",
        "`None`, let us change `friends.name` to `bad_match.person` in the select part of our code."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "42",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | person | budget | note\n",
            "^5K646P9... |        | 12     | Dan has no food restrictions.\n",
            "^GBSA866... | Alice  | 13     | Alice can't eat jam_pb_pancakes.\n",
            "^GBS0MQ3... | Alice  | 13     | Alice can't eat pancakes_with_jam.\n",
            "^EDPZCDK... | Bob    | 10     | Bob can't eat jam_pb_pancakes.\n",
            "^EDPQRGD... | Bob    | 10     | Bob can't eat pancakes_with_jam.\n",
            "^EDPJWHD... | Bob    | 10     | Bob can't eat scrambled_egg.\n",
            "^349T7Y9... | Carol  | 11     | Carol can't eat jam_pb_pancakes.\n",
            "^349Y3RY... | Carol  | 11     | Carol can't eat jam_pb_pancakes.\n",
            "^349T1BM... | Carol  | 11     | Carol can't eat pancakes_with_jam.\n",
            "^349GS41... | Carol  | 11     | Carol can't eat pb_jam_sandwich.\n"
          ]
        }
      ],
      "source": [
        "basic_customer_info = friends.join_left(\n",
        "    bad_match, friends.name == bad_match.person\n",
        ").select(\n",
        "    bad_match.person,\n",
        "    friends.budget,\n",
        "    note=pw.apply(\n",
        "        make_food_constraint_note_none_tolerant, friends.name, bad_match.dish\n",
        "    ),\n",
        ")\n",
        "pw.debug.compute_and_print(basic_customer_info)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "43",
      "metadata": {},
      "source": [
        "Note that, the cell that earlier included `Dan` is empty, even though `Dan` is the\n",
        "only value that could be included in this column (if there would be a match).\n",
        "The reason is that the expression for this column simply shows the value\n",
        "from `bad match.person`; since this row was not matched, this value is undefined and\n",
        "replaced by `None`.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "44",
      "metadata": {},
      "source": [
        "### On right joins\n",
        "In the examples above, you only relied on `join_left`. The [`join_right`](/developers/api-docs/pathway-table#pathway.Table.join_right) operation is quite\n",
        "similar in its behavior. Namely,"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "45",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | allergy\n",
            "^GBS8A5Y... | Alice | milk\n",
            "^EDPRSFF... | Bob   | eggs\n",
            "^349MSAF... | Carol | milk\n",
            "^349R9M9... | Carol | peanuts\n",
            "^5K646P9... | Dan   |\n",
            "            | name  | allergy\n",
            "^GBS3JRG... | Alice | milk\n",
            "^EDPTAZH... | Bob   | eggs\n",
            "^349WXJS... | Carol | milk\n",
            "^349V118... | Carol | peanuts\n",
            "^5K64NMM... | Dan   |\n"
          ]
        }
      ],
      "source": [
        "people_allergies = friends.join_left(\n",
        "    allergies, friends.name == allergies.person\n",
        ").select(friends.name, allergies.allergy)\n",
        "pw.debug.compute_and_print(people_allergies)\n",
        "\n",
        "# is almost (except for auto-generated IDs) equivalent to\n",
        "\n",
        "people_allergies = allergies.join_right(\n",
        "    friends, allergies.person == friends.name\n",
        ").select(friends.name, allergies.allergy)\n",
        "pw.debug.compute_and_print(people_allergies)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "46",
      "metadata": {},
      "source": [
        "When you join two tables, the only difference is in syntax - since allergies is\n",
        "the table on which you call `join_right`, it must be first argument\n",
        "in the join condition, i.e. it is"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "47",
      "metadata": {},
      "outputs": [],
      "source": [
        "allergies.person == friends.name"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "48",
      "metadata": {},
      "source": [
        "as opposed to"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "49",
      "metadata": {},
      "outputs": [],
      "source": [
        "friends.name == allergies.person"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "50",
      "metadata": {},
      "source": [
        "used in the `join_left`."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "51",
      "metadata": {},
      "source": [
        "### On full outer joins\n",
        "The [`join_outer`](/developers/api-docs/pathway-table#pathway.Table.join_outer) operation is a full outer join, which means that"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "52",
      "metadata": {},
      "outputs": [],
      "source": [
        "left.join_outer(right, *on)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "53",
      "metadata": {},
      "source": [
        "not only show the pairs of rows from left and right that meet the condition\n",
        "in `*on`, but also rows that didn't get matched with any other row,\n",
        "from both the `left` and `right` tables. To demonstrate this operation, let\n",
        "us introduce another group of friends and find out, for each person in a group,\n",
        "whether the other group has any people with the same name."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "54",
      "metadata": {},
      "outputs": [],
      "source": [
        "other_group = pw.debug.table_from_markdown(\n",
        "    \"\"\"\n",
        "    | name  |budget\n",
        "  5 | Bob   | 12\n",
        "  6 | Carol | 14\n",
        "  7 | Eve   | 12\n",
        " \"\"\"\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "55",
      "metadata": {
        "lines_to_next_cell": 2
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | l_name | l_id        | r_name | r_id\n",
            "^T0B6YY5... |        |             | Eve    | ^6A0QZMJ...\n",
            "^GBSDEY3... | Alice  | ^YYY4HAB... |        |\n",
            "^EDPVR2S... | Bob    | ^Z3QWT29... | Bob    | ^3S2X6B2...\n",
            "^349R4A9... | Carol  | ^3CZ78B4... | Carol  | ^A984WV0...\n",
            "^5K646P9... | Dan    | ^3HN31E1... |        |\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(\n",
        "    friends.join_outer(other_group, friends.name == other_group.name).select(\n",
        "        l_name=friends.name,\n",
        "        l_id=friends.id,\n",
        "        r_name=other_group.name,\n",
        "        r_id=other_group.id,\n",
        "    )\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "56",
      "metadata": {},
      "source": [
        "## ID inheritance in join\n",
        "Full (yet still informal) syntax of join is:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "57",
      "metadata": {},
      "outputs": [],
      "source": [
        "table.join(other, *on, id = None)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "58",
      "metadata": {},
      "source": [
        "where\n",
        "* `table` and `other` are tables to be joined,\n",
        "* `*on` is a list of conditions of form:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "59",
      "metadata": {},
      "outputs": [],
      "source": [
        "table.some_column == other.some_other_column"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "60",
      "metadata": {},
      "source": [
        "* optional `id` can be set to either table.id or other.id\n",
        "\n",
        "Whenever the `id` argument is not none, join will try to use the column passed in\n",
        "the id argument as the new id in the result of join.\n",
        "This operation will succeed only when there is a guarantee that the resulting joined table\n",
        "has no multiple rows with the same id-to-inherit.\n",
        "\n",
        "Below you can find three examples - one successful and two failed id inheritance.\n",
        "First, let us see what are the id-s of the original tables."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "61",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | budget\n",
            "^YYY4HAB... | Alice | 13\n",
            "^Z3QWT29... | Bob   | 10\n",
            "^3CZ78B4... | Carol | 11\n",
            "^3HN31E1... | Dan   | 12\n",
            "            | person | allergy\n",
            "^MPFCCEZ... | Alice  | milk\n",
            "^HTR084Q... | Bob    | eggs\n",
            "^V9MWYA4... | Carol  | milk\n",
            "^SJFASVQ... | Carol  | peanuts\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(friends)\n",
        "pw.debug.compute_and_print(allergies)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "62",
      "metadata": {},
      "source": [
        "### Successful id inheritance\n",
        "Let us try a join that inherits id-s from table `allergies`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "63",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | allergy | budget\n",
            "^MPFCCEZ... | Alice | milk    | 13\n",
            "^HTR084Q... | Bob   | eggs    | 10\n",
            "^V9MWYA4... | Carol | milk    | 11\n",
            "^SJFASVQ... | Carol | peanuts | 11\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(\n",
        "    allergies.join(friends, allergies.person == friends.name, id=allergies.id).select(\n",
        "        friends.name, allergies.allergy, friends.budget\n",
        "    )\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "64",
      "metadata": {},
      "source": [
        "As you can see, the id column is the same as in the `friends` table,\n",
        "which is not the case when the id parameter is not set."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "65",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | allergy | budget\n",
            "^GBS3JRG... | Alice | milk    | 13\n",
            "^EDPTAZH... | Bob   | eggs    | 10\n",
            "^349WXJS... | Carol | milk    | 11\n",
            "^349V118... | Carol | peanuts | 11\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(\n",
        "    allergies.join(friends, allergies.person == friends.name).select(\n",
        "        friends.name, allergies.allergy, friends.budget\n",
        "    )\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "66",
      "metadata": {},
      "source": [
        "### Failed id inheritance: duplicate id-s\n",
        "The first possible problem with inheriting id is that one row of the source table\n",
        "could be matched with several entries of the other table. The code below will\n",
        "cause such a problem, and will raise a `KeyError` error."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "67",
      "metadata": {},
      "outputs": [],
      "source": [
        "pw.debug.compute_and_print(\n",
        "    allergies.join(friends, allergies.person == friends.name, id=friends.id).select(\n",
        "        friends.name, allergies.allergy\n",
        "    )\n",
        ")\n",
        "[stacktrace...]\n",
        "KeyError: 'duplicate key: ^SERVYWW6KDGEQ2WVZ3ZZB86VSR'"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "68",
      "metadata": {
        "lines_to_next_cell": 2
      },
      "source": [
        "### Failed id inheritance: empty id-s\n",
        "Finally, if you consider outer joins between those two tables, you\n",
        "may encounter a situation in which you need to assign an id that is empty in the join result:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "69",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | allergy | allergies_id\n",
            "^GBS3JRG... | Alice | milk    | ^MPFCCEZ...\n",
            "^EDPTAZH... | Bob   | eggs    | ^HTR084Q...\n",
            "^349WXJS... | Carol | milk    | ^V9MWYA4...\n",
            "^349V118... | Carol | peanuts | ^SJFASVQ...\n",
            "^5K64NMM... | Dan   |         |\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(\n",
        "    allergies.join_right(friends, allergies.person == friends.name).select(\n",
        "        friends.name, allergies.allergy, allergies_id=allergies.id\n",
        "    )\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "70",
      "metadata": {},
      "source": [
        "As you can see, the `allergies_id` field is not set for Dan's entry. If you try to use\n",
        "`allergies.id` as the `id` parameter, you will encounter a `TypeError` error."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "71",
      "metadata": {},
      "outputs": [],
      "source": [
        "pw.debug.compute_and_print(\n",
        "    allergies.join_right(\n",
        "        friends, allergies.person == friends.name, id=allergies.id\n",
        "    ).select(friends.name, allergies.allergy, allergies_id=allergies.id)\n",
        ")\n",
        "[stacktrace...]\n",
        "TypeError: type mismatch: expected a pointer, got None"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "72",
      "metadata": {},
      "source": [
        "## Joins on a foreign key\n",
        "In Pathway, the id column is auto-generated, and as such joining over a foreign key kept in\n",
        "some other table requires extra care. Let's assume that you have another table `likes`\n",
        "that indicates that a friend (row in `friends`) likes some particular dish (row in `menu`)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "73",
      "metadata": {},
      "outputs": [],
      "source": [
        "likes = pw.debug.table_from_markdown(\n",
        "    \"\"\"\n",
        "    | f_id  | m_id\n",
        "100 | 1     | 11\n",
        "101 | 1     | 13\n",
        "102 | 2     | 12\n",
        "103 | 2     | 13\n",
        "104 | 3     | 11\n",
        "105 | 3     | 14\n",
        "106 | 3     | 13\n",
        "107 | 4     | 12\n",
        "108 | 4     | 14\n",
        "\"\"\"\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "74",
      "metadata": {},
      "source": [
        "Without further specification, Pathway treats columns `f_id` and `m_id` as numbers:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "75",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | f_id | m_id\n",
            "^1BNYXQH... | 1    | 11\n",
            "^6J3K9CB... | 1    | 13\n",
            "^N3QENAM... | 2    | 12\n",
            "^H4W0S32... | 2    | 13\n",
            "^EQXAJMB... | 3    | 11\n",
            "^XEGKSQC... | 3    | 13\n",
            "^S57QPSB... | 3    | 14\n",
            "^MVKXDSS... | 4    | 12\n",
            "^VVA8C71... | 4    | 14\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(likes)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "76",
      "metadata": {},
      "source": [
        "while the id of tables `friends` and `menu` was converted to Pointer.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "77",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | budget\n",
            "^YYY4HAB... | Alice | 13\n",
            "^Z3QWT29... | Bob   | 10\n",
            "^3CZ78B4... | Carol | 11\n",
            "^3HN31E1... | Dan   | 12\n",
            "            | dish              | price\n",
            "^3J2R55X... | jam_pb_pancakes   | 12\n",
            "^03PYXDQ... | pancakes_with_jam | 11\n",
            "^GFDBR0G... | pb_jam_sandwich   | 9\n",
            "^XTPZRQ2... | scrambled_egg     | 11\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(friends)\n",
        "pw.debug.compute_and_print(menu)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "78",
      "metadata": {},
      "source": [
        "\n",
        "To handle joins using those columns, you can use [`pointer_from`](/developers/api-docs/pathway-table#pathway.Table.pointer_from) function"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "79",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | f_id | m_id | f_id_ptr    | m_id_ptr\n",
            "^1BNYXQH... | 1    | 11   | ^YYY4HAB... | ^03PYXDQ...\n",
            "^6J3K9CB... | 1    | 13   | ^YYY4HAB... | ^3J2R55X...\n",
            "^N3QENAM... | 2    | 12   | ^Z3QWT29... | ^GFDBR0G...\n",
            "^H4W0S32... | 2    | 13   | ^Z3QWT29... | ^3J2R55X...\n",
            "^EQXAJMB... | 3    | 11   | ^3CZ78B4... | ^03PYXDQ...\n",
            "^XEGKSQC... | 3    | 13   | ^3CZ78B4... | ^3J2R55X...\n",
            "^S57QPSB... | 3    | 14   | ^3CZ78B4... | ^XTPZRQ2...\n",
            "^MVKXDSS... | 4    | 12   | ^3HN31E1... | ^GFDBR0G...\n",
            "^VVA8C71... | 4    | 14   | ^3HN31E1... | ^XTPZRQ2...\n"
          ]
        }
      ],
      "source": [
        "likes += likes.select(\n",
        "    f_id_ptr=friends.pointer_from(likes.f_id),\n",
        "    m_id_ptr=menu.pointer_from(likes.m_id),\n",
        ")\n",
        "pw.debug.compute_and_print(likes)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "80",
      "metadata": {},
      "source": [
        "An [example of code](#long-chain-example) joining `friends` with `menu` using `likes`\n",
        "is presented the next section on chaining joins."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "81",
      "metadata": {},
      "source": [
        "## Chaining joins:\n",
        "Pathway provides two ways of chaining joins. The first relies on usage of [`pw.this`](/developers/api-docs/pathway#pathway.this),\n",
        "the second allows for slightly more compact code. Below, let's focus on chaining joins\n",
        "using `pw.left`.\n",
        "\n",
        " ### Simple join chaining\n",
        "Below, you will do chain joins using [`pw.left`](/developers/api-docs/pathway#pathway.left) and [`pw.right`](/developers/api-docs/pathway#pathway.right). To show how it can be used, let's revisit\n",
        "the first example of `join_left`, in which you computed a `join_left` on a table\n",
        "that was obtained by another `join_left`. Instead of storing the result of the first\n",
        "`join_left` in `people_allergies`, you can use the following:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "82",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | dish\n",
            "^AWS7HPF... | Alice | jam_pb_pancakes\n",
            "^AWS98F7... | Alice | pancakes_with_jam\n",
            "^RGCBXCM... | Bob   | jam_pb_pancakes\n",
            "^RGCAFYZ... | Bob   | pancakes_with_jam\n",
            "^RGCEXE8... | Bob   | scrambled_egg\n",
            "^AWS822S... | Carol | jam_pb_pancakes\n",
            "^Q5HTZJD... | Carol | jam_pb_pancakes\n",
            "^AWSF9MS... | Carol | pancakes_with_jam\n",
            "^Q5HVB48... | Carol | pb_jam_sandwich\n",
            "^VFP6CNZ... | Dan   |\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(\n",
        "    friends.join_left(allergies, friends.name == allergies.person)\n",
        "    .select(friends.name, allergies.allergy, friends.budget)\n",
        "    .join_left(allergens_in_menu, pw.left.allergy == pw.right.allergen)\n",
        "    .select(pw.this.name, allergens_in_menu.dish)\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "83",
      "metadata": {},
      "source": [
        "Essentially, for a join `left.join(right, *on)`, `pw.left` allows us to address the `left` table\n",
        "and `pw.right` allows us to address the `right` table. In this particular example, `pw.left` allows us to address the table computed by\n",
        "\n",
        "      friends.join_left(allergies, friends.name == allergies.person\n",
        "          ).select(friends.name, allergies.allergy)\n",
        "\n",
        "without breaking the chain and storing intermediate results in a temporary\n",
        "variable.\n",
        "\n",
        "More generally, given a chain:\n",
        "\n",
        "_MD_SHOW_table.join(...).select(...).join(...).select(...)...\n",
        "\n",
        "`pw.left` can be used to address the result of the latest select.\n",
        "\n",
        "While in the example above `pw.right` is essentially a replacement for `allergens_in_menu`,\n",
        "it can be also used to address a table that is passed as an argument of a join, but is\n",
        "not assigned to any variable.\n",
        "\n",
        "To show `pw.right` in action, you can go back to our [example](#expressions-using-unmatched-rows) showing basic consumer information,\n",
        "and compute it directly from tables `friends`, `allergies`, and `allergens_in_menu`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "84",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | budget | note\n",
            "^GBSA866... | Alice | 13     | Alice can't eat jam_pb_pancakes.\n",
            "^GBS0MQ3... | Alice | 13     | Alice can't eat pancakes_with_jam.\n",
            "^EDPZCDK... | Bob   | 10     | Bob can't eat jam_pb_pancakes.\n",
            "^EDPQRGD... | Bob   | 10     | Bob can't eat pancakes_with_jam.\n",
            "^EDPJWHD... | Bob   | 10     | Bob can't eat scrambled_egg.\n",
            "^349T7Y9... | Carol | 11     | Carol can't eat jam_pb_pancakes.\n",
            "^349Y3RY... | Carol | 11     | Carol can't eat jam_pb_pancakes.\n",
            "^349T1BM... | Carol | 11     | Carol can't eat pancakes_with_jam.\n",
            "^349GS41... | Carol | 11     | Carol can't eat pb_jam_sandwich.\n",
            "^5K646P9... | Dan   | 12     | Dan has no food restrictions.\n"
          ]
        }
      ],
      "source": [
        "basic_customer_info = friends.join_left(\n",
        "    allergies.join(\n",
        "        allergens_in_menu, allergies.allergy == allergens_in_menu.allergen\n",
        "    ).select(\n",
        "        allergens_in_menu.dish,\n",
        "        allergies.person,\n",
        "    ),\n",
        "    friends.name == pw.right.person,\n",
        ").select(\n",
        "    friends.name,\n",
        "    friends.budget,\n",
        "    note=pw.apply(make_food_constraint_note_none_tolerant, friends.name, pw.right.dish),\n",
        ")\n",
        "pw.debug.compute_and_print(basic_customer_info)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "85",
      "metadata": {},
      "source": [
        "### Long chain example\n",
        "To demonstrate longer chains in action, let's go back to the table `likes` you used to\n",
        "show how to handle (generate) foreign keys."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "86",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | budget | dish              | price | allergy | allergen\n",
            "^43TEY2A... | Alice | 13     | jam_pb_pancakes   | 12    | milk    | eggs\n",
            "^43TA1DN... | Alice | 13     | jam_pb_pancakes   | 12    | milk    | peanuts\n",
            "^7MY5F50... | Alice | 13     | pancakes_with_jam | 11    | milk    | eggs\n",
            "^3173P8A... | Bob   | 10     | pb_jam_sandwich   | 9     | eggs    | peanuts\n",
            "^7MY1KKF... | Carol | 11     | pancakes_with_jam | 11    | milk    | eggs\n",
            "^7MY5MKG... | Carol | 11     | pancakes_with_jam | 11    | peanuts | eggs\n",
            "^7MY2F5D... | Carol | 11     | pancakes_with_jam | 11    | peanuts | milk\n",
            "^417WS9H... | Carol | 11     | scrambled_egg     | 11    | milk    | eggs\n",
            "^417NSZY... | Carol | 11     | scrambled_egg     | 11    | peanuts | eggs\n",
            "^3179040... | Dan   | 12     | pb_jam_sandwich   | 9     |         | peanuts\n",
            "^417SEKP... | Dan   | 12     | scrambled_egg     | 11    |         | eggs\n"
          ]
        }
      ],
      "source": [
        "feasible_choice = (\n",
        "    friends.join(likes, friends.id == likes.f_id_ptr)\n",
        "    .select(friends.name, friends.budget, likes.m_id_ptr)\n",
        "    .join(menu, pw.left.m_id_ptr == menu.id)\n",
        "    .select(pw.left.name, pw.left.budget, menu.dish, menu.price)\n",
        "    .join_left(allergies, pw.left.name == pw.right.person)\n",
        "    .select(*pw.left[[\"name\", \"budget\", \"dish\", \"price\"]], allergies.allergy)\n",
        "    .join(allergens_in_menu, pw.left.dish == pw.right.dish)\n",
        "    .select(\n",
        "        *pw.left[[\"name\", \"budget\", \"dish\", \"price\", \"allergy\"]],\n",
        "        allergens_in_menu.allergen,\n",
        "    )\n",
        "    .filter((pw.this.price <= pw.this.budget) & (pw.this.allergy != pw.this.allergen))\n",
        ")\n",
        "pw.debug.compute_and_print(feasible_choice)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "87",
      "metadata": {},
      "source": [
        "As you can see, this table contains all choices of person and dish, such that a person likes\n",
        "a particular dish, is not allergic to it, and can afford it. You can further simplify\n",
        "the result by adding another groupby-reduce at the end of the chain."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "88",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | dish              | budget | price\n",
            "^57TKD0K... | Alice | jam_pb_pancakes   | 13     | 12\n",
            "^D5737FK... | Alice | pancakes_with_jam | 13     | 11\n",
            "^ZR14WYB... | Bob   | pb_jam_sandwich   | 10     | 9\n",
            "^B7MB26C... | Carol | pancakes_with_jam | 11     | 11\n",
            "^8A89VGQ... | Carol | scrambled_egg     | 11     | 11\n",
            "^B20HZPG... | Dan   | pb_jam_sandwich   | 12     | 9\n",
            "^P1E97MY... | Dan   | scrambled_egg     | 12     | 11\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(\n",
        "    feasible_choice.groupby(\n",
        "        *pw.this[[\"name\", \"dish\", \"budget\", \"price\"]],\n",
        "    ).reduce(*pw.this[[\"name\", \"dish\", \"budget\", \"price\"]])\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "89",
      "metadata": {},
      "source": [
        "Furthermore, one can make this piece of code more compact, using the [* notation](/developers/user-guide/data-transformation/table-operations#select-and-notations)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "90",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | name  | budget | dish              | price | allergy | allergen\n",
            "^GR3JTK7... | Alice | 13     | jam_pb_pancakes   | 12    | milk    | eggs\n",
            "^560S7R7... | Alice | 13     | jam_pb_pancakes   | 12    | milk    | peanuts\n",
            "^6AB73E4... | Alice | 13     | pancakes_with_jam | 11    | milk    | eggs\n",
            "^SVK8374... | Bob   | 10     | pb_jam_sandwich   | 9     | eggs    | peanuts\n",
            "^HZ6CV02... | Carol | 11     | pancakes_with_jam | 11    | milk    | eggs\n",
            "^F7XY2H0... | Carol | 11     | pancakes_with_jam | 11    | peanuts | eggs\n",
            "^XCB28V3... | Carol | 11     | pancakes_with_jam | 11    | peanuts | milk\n",
            "^NVJZ7EM... | Carol | 11     | scrambled_egg     | 11    | milk    | eggs\n",
            "^Q517DE3... | Carol | 11     | scrambled_egg     | 11    | peanuts | eggs\n",
            "^SKZJ2PT... | Dan   | 12     | pb_jam_sandwich   | 9     |         | peanuts\n",
            "^E5P13D5... | Dan   | 12     | scrambled_egg     | 11    |         | eggs\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(\n",
        "    friends.join(likes, friends.id == likes.f_id_ptr)\n",
        "    .select(*friends, likes.m_id_ptr)\n",
        "    .join(menu, pw.left.m_id_ptr == menu.id)\n",
        "    .select(*pw.left.without(\"m_id_ptr\"), *menu)\n",
        "    .join_left(allergies, pw.left.name == pw.right.person)\n",
        "    .select(*pw.left, allergies.allergy)\n",
        "    .join(allergens_in_menu, pw.left.dish == pw.right.dish)\n",
        "    .select(*pw.left, allergens_in_menu.allergen)\n",
        "    .filter((pw.this.price <= pw.this.budget) & (pw.this.allergy != pw.this.allergen))\n",
        "    .groupby(*pw.this)\n",
        "    .reduce(*pw.this)\n",
        ")"
      ]
    }
  ],
  "metadata": {
    "jupytext": {
      "cell_metadata_filter": "-all",
      "main_language": "python",
      "notebook_metadata_filter": "-all"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.11.14"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}